package model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.dto.Match;
import utility.Database;
import utility.Log;

public class MatchModel extends Database {
	public boolean insert(Match dto) {
		String sql = "insert into tbl_match(match_id,goal_home,goal_away,fixture_id) values(null,?,?,?)";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, dto.getGoalHome());
			pstmt.setInt(2, dto.getGoalAway());
			pstmt.setInt(3, dto.getFixtureId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) { 
			Log.write(e);
		} finally {
			this.close();
		}

		return false;
	}

	public boolean delete(int match_id) {
		String sql = "delete from tbl_match where match_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, match_id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public boolean update(Match dto) {
		String sql = "update tbl_match set goal_home=?,goal_away=? where match_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, dto.getGoalHome());
			pstmt.setInt(2, dto.getGoalAway());
			pstmt.setInt(3, dto.getMatchId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public Match get(int match_id) {
		String sql = "select * from tbl_match where match_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, match_id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				Match dto = new Match();
				dto.setMatchId(match_id);
				dto.setGoalHome(rs.getInt("goal_home"));
				dto.setGoalAway(rs.getInt("goal_away"));
				
				return dto;
			}
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(String search, int limit, int page) {
		String sql = "select * from tbl_country where country_name like ? ORDER BY country_id DESC limit ?,?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			pstmt.setInt(2, (page - 1) * limit);
			pstmt.setInt(3, limit);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(int limit, int page) {
		return list("", limit, page);
	}

	public int count(String search) {
		String sql = "select count(*) from tbl_country where country_name like ?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			ResultSet rs = pstmt.executeQuery();
			return rs.next() ? rs.getInt(1) : 0;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return 0;
	}

	public int count() {
		return count("");
	}
	public static void main(String[] args) {
		Match dto=new Match();
		MatchModel dao=new MatchModel();
		dto.setGoalHome(5);
		dto.setGoalAway(2);
		dto.setFixtureId(4);
		System.out.println(dao.insert(dto));
	}

}
